﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Data
{
    public class Conexao
    {

        private static Conexao instance;

        public SqlConnection conexao()
        {
            //Data Source=(local);Initial Catalog=FARMACIA;User ID=vitor
            //string conec = "Data Source=(local);Initial Catalog=FARMACIA;User ID=vitor;Password=123;Language=Portuguese";
            
            //string conec = "Data Source=(local);Initial Catalog=FARMACIA;Integrated Security=True";

            string conec = "Data Source=.\\SQLEXPRESS;AttachDbFilename=F:\\TCC\\Banco\\BancoTCC.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            
            SqlConnection cn = new SqlConnection(conec);

            return cn;
        }

        public static Conexao getInstance
        {
            get
            {
                if (instance == null)
                {
                    instance = new Conexao();
                }
                return instance;
            }
        }

        public SqlConnection abrirConexao()
        {
            SqlConnection cn = conexao();
            try
            {
                cn.Open();
                return cn;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void fecharConexao(SqlConnection cn)
        {
            try
            {
                cn.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataTable executeQuery(string sql)
        {
            try
            {
                SqlCommand sqlComm = new SqlCommand(sql, abrirConexao());

                sqlComm.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(sqlComm);

                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public string executeQueryComStringRetorno(string sql)
        {
            try
            {
                string dado;

                SqlCommand sqlComm = new SqlCommand(sql, abrirConexao());

                sqlComm.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(sqlComm);

                DataTable dt = new DataTable();
                da.Fill(dt);

                dado = dt.Rows[0][0].ToString();

                return dado;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void execute(string sql)
        {
            try
            {
               

                SqlCommand sqlComm = new SqlCommand(sql, abrirConexao());

                sqlComm.ExecuteNonQuery();
               
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}

